#coding:utf-8
'''
数据库记录时间查询

Created on 2010-8-27

@author: lijun
'''

import MySQLdb

import os
os.environ['DJANGO_SETTINGS_MODULE'] = 'settings'

import sys
sys.path.append('..')
import conf



def SearchLatestDate(modelObj, host):
    assert modelObj
    assert host
    # 按时间倒序查询，限制只返回第一条记录，即最新时间
    try:
        latestDateTime = modelObj.objects.filter(server=host).order_by('-date_time')[0].date_time
        return latestDateTime
    except IndexError,e:
        # 当数据库记录为空时会抛出此异常，则直接返回None
        print e
        return 
    except Exception,e:
        print e
        raise
        
def SearchOldestDate(modelObj, host):
    assert modelObj
    assert host
    # 按时间正序查询，限制只返回第一条记录，即最早时间
    try:
        oldestDateTime = modelObj.objects.filter(server=host).order_by('date_time')[0].date_time 
        return oldestDateTime
    except IndexError,e:
        # 当数据库记录为空时会抛出此异常，则直接返回None
        print e
        return 
    except Exception,e:
        print e
        raise
    
def CreateSelectSql(fields, table, wheres=None, ext=None):  
    
    sql = 'select %s from %s' % (fields, table)
    if wheres != None:
        sql += ' where %s' % wheres
    if ext != None:
        sql += ' %s' % ext
    
    return sql
        
def GetSelectSql(selectItems, froms, where=None, ext=None):
    '''构造select sql 语句。selectItems 须为一个字符串序列。
    '''
        
    sql = 'select '
    for item in selectItems:
        sql += item + ','
    sql = sql[:-1]
        
    sql += ' from %s ' % froms
    if where != None:
        sql += ' where %s ' % where
    
    if ext != None:
        sql += ext
        
    return sql
    
    
    
    
        
def CreateInsertSql(table, fields):
    assert table
    assert fields
    
    sql = 'insert into %s' % table
    fieldsStr = '('
    valuesStr = '('
    
    for field in fields:
        fieldsStr += field + ','
        valuesStr += '%s' + ','
    fieldsStr = fieldsStr[:-1]
    valuesStr = valuesStr[:-1]
    fieldsStr += ')'
    valuesStr += ')'
    sql += fieldsStr + ' values ' + valuesStr
    return sql
    
    
    
    
def InsertMultiDB(table, fields, values):
    '''批量插入数据库
    '''
    cursor = None
    conn = None
    try:    
        cursor,conn = GetCursor()
        assert len(fields) == len(values[0])
        sql = CreateInsertSql(table, fields)
        count = cursor.executemany(sql, values)
        Commit(conn)
        return count
    except Exception, e:
        raise
    finally:
        CloseDB(cursor, conn)
        
        
def SelectDB(sql):
    cursor = None
    conn = None    
    try:
        cursor, conn = GetCursor()
        cursor.execute(sql)
        datas = cursor.fetchall()
        Commit(conn)
        return datas
    except Exception, e:
        raise
    finally:
        CloseDB(cursor, conn)
        
        
    
            
def ExecuteSql(sql):
    cursor = None
    conn = None
    try:
        cursor, conn = GetCursor()
        num = cursor.execute(sql)
        Commit(conn)
        return num
    except Exception, e:
        raise
    finally:
        CloseDB(cursor, conn)
           
    
    
        
def GetCursor(host=conf.DB_HOST, user=conf.DB_USER, passwd=conf.DB_PASSWORD,
                   dbname=conf.DB_NAME):
     #连接数据库
    try:
        conn = MySQLdb.connect(host=host, user=user, passwd=passwd, db=dbname)
        cursor = conn.cursor()
        
        return cursor,conn
    except Exception, e:
        raise
      
      
      
def Commit(conn):
    
    if conn is not None:
        try:
            conn.commit()            
        except Exception:
            conn.rollback()
            raise
              
        
def CloseDB(cursor, conn):
    try:
        # close database
        if cursor is not None:
            cursor.close()
        if conn is not None:
            conn.close()
    except:
        pass
        
        
        
        
if __name__ == '__main__':      
    fields = ('attr_name', 'attr_value', 'nodeInfo_id') 
    values = [ ('age', '25', '999999'), ('sex', 'female', '999998')]
    table='logInfo_htmlconverterattributeinfo'    
    
#    print InsertMultiDB(table=table, fields=fields, values=values)
    
    
#    sql= 'select * from logInfo_htmlconverterattributeinfo limit 100'
    
#    table = conf.TABLE_HtmlconverterMainInfo
    beginDate = '2010-09-01 01:00:00'
    endDate = '2010-09-01 20:00:00'
    host = '58.68.150.203'
    typeValue = 'file2txt'
    dateFormat = "%Y-%m-%d %H:%i:00"
    
    sql = "select sum(count1), server2, code2, date_time2, view2,type2 from \
     (select count(*) as count1,view as view2, type as type2, \
     server as server2,code as code2,( DATE_FORMAT(date_time, \"%s\"))\
      as date_time2  from %s \
      where date_time >= '%s' and date_time < '%s'\
      and server = '%s' and type = '%s' \
      group by date_time) as t2 \
      group by server2, code2, date_time2, view2, type2" % (dateFormat, table, 
                                    beginDate, endDate, host, typeValue)
    
    sql2 = "select sum(time1)/sum(count1), server2, code2, date_time2, view2,type2 from \
     (select count(*) as count1, sum(time) as time1, view as view2, type as type2, \
     server as server2,code as code2,( DATE_FORMAT(date_time, \"%s\"))\
      as date_time2  from %s \
      where date_time >= '%s' and date_time < '%s'\
      and server = '%s' and type = '%s' and code='OK' \
      group by date_time) as t2 \
      group by server2, code2, date_time2, view2, type2" % (dateFormat, table, 
                                    beginDate, endDate, host, typeValue)    
    
    
#    print SelectDB(sql)
#    print SelectDB(sql2)
    
#    print SearchLatestDate(DsAgentMainInfo, '58.68.150.203')
#    print SearchOldestDate(DsAgentMainInfo, '58.68.150.203')

    print CreateSelectSql(fields='*', table='user', wheres='id > 10', ext='limit 100')



    